import pandas as pd
import os
from datetime import datetime

df = pd.read_excel('./全部A股.xlsx', sheet_name='筛选结果', dtype=str)
df['股票代码'] = df['证券代码'].apply(lambda x: x.split('.')[0])
print(df.head())

bus_oppr = pd.read_excel(sorted([i for i in os.listdir() if '跨境商机' in i])[-2], dtype=str)
print(bus_oppr.columns)
print(bus_oppr.head())

ret = pd.merge(left=df, right=bus_oppr, left_on='股票代码', right_on='股票代码', how='inner')

ret.drop(columns=['股票代码'])


ret.to_excel(f'./output/最终结果_{datetime.now().strftime("%Y%m%d")}.xlsx', sheet_name='解析结果', index=False)

# 读取全部A股数据
all_a = pd.read_excel('./全部A股.xlsx', sheet_name='全部A股', dtype=str)

# 获取ret中的证券代码列表
ret_codes = ret['股票代码'].unique().tolist()

# 从all_a中筛选出包含在ret中的证券代码的记录
basic_info = all_a[all_a['证券代码'].apply(lambda x: x.split('.')[0] in ret_codes)]

# 创建ExcelWriter对象以写入多个sheet
with pd.ExcelWriter(f'./output/最终结果_{datetime.now().strftime("%Y%m%d")}.xlsx') as writer:
    # 写入解析结果sheet
    ret.to_excel(writer, sheet_name='解析结果', index=False)
    # 写入基本信息sheet
    basic_info.to_excel(writer, sheet_name='基本信息', index=False)

print(f"已将基本信息保存至'基本信息'sheet，共{len(basic_info)}条记录")
